﻿#include <QAxObject>
#include <QFile>
#include <QStringList>
#include <QDebug>
#include <QDir>
#include "qexcel.h"

bool QExcel::isValid()
{
    return excel && workBooks && workBook && sheets;
}

QExcel::QExcel(QString xlsFilePath, QObject *parent)
    :excel(nullptr),
      workBooks(nullptr),
      workBook(nullptr),
      sheets(nullptr),
      sheet(nullptr)
{
    excel = new QAxObject( parent);
    if ( !excel->setControl("Excel.Application") )
    {
        excel->setControl("ket.Application");  //连接Excel控件
    }
    excel->dynamicCall("SetVisible (bool Visible)",  "true");

    workBooks = excel->querySubObject("Workbooks");
    QFile file(xlsFilePath);
    if (file.exists())
    {

        workBooks->dynamicCall("Open(const QString&)", QDir::toNativeSeparators(xlsFilePath) );
        workBook = excel->querySubObject("ActiveWorkBook");
        sheets = workBook->querySubObject("WorkSheets");
    }
}

QExcel::~QExcel()
{
    close();
}

QList<QList<QVariant> >Variant2ListListVariant(const QVariant &var)
{
    QList<QList<QVariant> >ret;
    QVariantList varRows = var.toList();
    if(varRows.isEmpty())
    {
        return ret;
    }
    const int rowCount = varRows.size();
    QVariantList rowData;
    for(int i = 0; i < rowCount; ++i)
    {
        rowData = varRows[i].toList();
        ret.push_back(rowData);
    }
    return ret;
}

QString ConvertFromNumber(int number)
{
    QString resultStr = "";
    while(number > 0)
    {
        int k = number % 26;
        if(k == 0)
            k = 26;
        resultStr.push_front(QChar(k + 64));
        number = (number - k) / 26;
    }

    return resultStr;
}

QString rowColStr(int row, int col)
{
    QString ret = ConvertFromNumber(col);
    ret += QString::number(row);

    return ret;
}

void QExcel::setRangeValue(QPair<int,int> start, QPair<int,int> end, QList< QList<QVariant> > data)
{

    if(sheet == nullptr) return;
    QList<QVariant> list;
    const int rowCount = data.size();
    for(int i = 0; i < rowCount; i++)
    {
        list.push_back(QVariant(data[i]));
    }

    int row1 = start.first;
    int col1 = start.second;
    int row2 = end.first;
    int col2 = end.second;

    QString rowCol = rowColStr(row1, col1) + ":" + rowColStr(row2, col2);
    qDebug() << rowCol;
    QAxObject *usedRange = sheet->querySubObject("Range(const QString&)", rowCol);

    usedRange->setProperty("Value", QVariant(list));
}

QList<QList<QVariant>> QExcel::getRangeValue(QPair<int,int> start, QPair<int,int> end)
{
    QList<QList<QVariant>> ret;
    if(sheet == nullptr) return ret;
    int row1 = start.first;
    int col1 = start.second;
    int row2 = end.first;
    int col2 = end.second;

    QString rowCol = rowColStr(row1, col1) + ":" + rowColStr(row2, col2);
    qDebug() << rowCol;
    QAxObject *usedRange = sheet->querySubObject("Range(const QString&)", rowCol);

    if(nullptr == usedRange || usedRange->isNull())
    {
        return  ret;
    }
    QVariant var = usedRange->property("Value");
    delete usedRange;
    return Variant2ListListVariant(var);
}

void QExcel::close()
{
    excel->dynamicCall("Quit()");

    if(sheet) delete sheet;
    if(sheets) delete sheets;
    if(workBook) delete workBook;
    if(workBooks) delete workBooks;
    if(excel) delete excel;

    excel = nullptr;
    workBooks = nullptr;
    workBook = nullptr;
    sheets = nullptr;
    sheet = nullptr;
}

QAxObject *QExcel::getWorkBooks()
{
    return workBooks;
}

QAxObject *QExcel::getWorkBook()
{
    return workBook;
}

QAxObject *QExcel::getWorkSheets()
{
    return sheets;
}

QAxObject *QExcel::getWorkSheet()
{
    return sheet;
}

void QExcel::selectSheet(const QString& sheetName)
{
    if(sheets == nullptr) return;
    sheet = sheets->querySubObject("Item(const QString&)", sheetName);
}

void QExcel::deleteSheet(const QString& sheetName)
{
    if(sheets == nullptr) return;
    QAxObject * a = sheets->querySubObject("Item(const QString&)", sheetName);
    a->dynamicCall("delete");
}

void QExcel::deleteSheet(int sheetIndex)
{
    if(sheets == nullptr) return;
    QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex);
    a->dynamicCall("delete");
}

void QExcel::selectSheet(int sheetIndex)
{
    if(sheets == nullptr) return;
    sheet = sheets->querySubObject("Item(int)", sheetIndex);
}

void QExcel::setCellString(int row, int column, const QString& value)
{
    if(sheet == nullptr) return;
    QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column);
    range->dynamicCall("SetValue(const QString&)", value);
}

void QExcel::setCellFontBold(int row, int column, bool isBold)
{
    if(sheet == nullptr) return;
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Bold", isBold);
}

void QExcel::setColValue(int row, int col, QList<QVariant> data)
{
    if(sheet == nullptr) return;
    QString col_str = ConvertFromNumber(col);
    int n = data.size();
    QString rangeStr = col_str + QString::number(row) + ":" + col_str + QString::number(row + n - 1);

    qDebug() << rangeStr;

    QAxObject *user_rang = sheet->querySubObject("Range(const QString&)", rangeStr);
    user_rang->dynamicCall("SetValue(const QVariant&)", QVariant(data));
}

void QExcel::setCellFontSize(int row, int column, int size)
{
    if(sheet == nullptr) return;
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Size", size);
}

void QExcel::mergeCells(const QString& cell)
{
    if(sheet == nullptr) return;
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("VerticalAlignment", -4108);//xlCenter
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
}

void QExcel::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn)
{
    if(sheet == nullptr) return;
    QString cell;
    cell.append(QChar(topLeftColumn - 1 + 'A'));
    cell.append(QString::number(topLeftRow));
    cell.append(":");
    cell.append(QChar(bottomRightColumn - 1 + 'A'));
    cell.append(QString::number(bottomRightRow));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("VerticalAlignment", -4108);//xlCenter
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
}

QVariant QExcel::getCellValue(int row, int column)
{
    if(sheet == nullptr) return QVariant();
    QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column);
    return range->property("Value");
}

void QExcel::saveAs(QString fileName)
{
    workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(fileName));
}

void QExcel::save()
{
    workBook->dynamicCall("Save()");
}

int QExcel::getSheetsCount()
{
    return sheets->property("Count").toInt();
}

QString QExcel::getSheetName()
{
    return sheet->property("Name").toString();
}

QString QExcel::getSheetName(int sheetIndex)
{
    QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex);
    return a->property("Name").toString();
}

void QExcel::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn)
{
    QAxObject *usedRange = sheet->querySubObject("UsedRange");
    *topLeftRow = usedRange->property("Row").toInt();
    *topLeftColumn = usedRange->property("Column").toInt();

    QAxObject *rows = usedRange->querySubObject("Rows");
    *bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1;

    QAxObject *columns = usedRange->querySubObject("Columns");
    *bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1;
}

void QExcel::setColumnWidth(int column, int width)
{
    QString columnName;
    columnName.append(QChar(column - 1 + 'A'));
    columnName.append(":");
    columnName.append(QChar(column - 1 + 'A'));

    QAxObject * col = sheet->querySubObject("Columns(const QString&)", columnName);
    col->setProperty("ColumnWidth", width);
}

void QExcel::setCellTextCenter(int row, int column)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
}

void QExcel::setCellTextWrap(int row, int column, bool isWrap)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("WrapText", isWrap);
}

void QExcel::setAutoFitRow(int row)
{
    QString rowsName;
    rowsName.append(QString::number(row));
    rowsName.append(":");
    rowsName.append(QString::number(row));

    QAxObject * rows = sheet->querySubObject("Rows(const QString &)", rowsName);
    rows->dynamicCall("AutoFit()");
}

void QExcel::insertSheet(QString sheetName)
{
    sheets->querySubObject("Add()");
    QAxObject * a = sheets->querySubObject("Item(int)", 1);
    a->setProperty("Name", sheetName);
}

void QExcel::mergeSerialSameCellsInAColumn(int column, int topRow)
{
    int a,b,c,rowsCount;
    getUsedRange(&a, &b, &rowsCount, &c);

    int aMergeStart = topRow, aMergeEnd = topRow + 1;

    QString value;
    while(aMergeEnd <= rowsCount)
    {
        value = getCellValue(aMergeStart, column).toString();
        while(value == getCellValue(aMergeEnd, column).toString())
        {
            clearCell(aMergeEnd, column);
            aMergeEnd++;
        }
        aMergeEnd--;
        mergeCells(aMergeStart, column, aMergeEnd, column);

        aMergeStart = aMergeEnd + 1;
        aMergeEnd = aMergeStart + 1;
    }
}

void QExcel::clearCell(int row, int column)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("ClearContents()");
}

void QExcel::clearCell(const QString& cell)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("ClearContents()");
}

int QExcel::getUsedRowsCount()
{
    QAxObject *usedRange = sheet->querySubObject("UsedRange");
    int topRow = usedRange->property("Row").toInt();
    QAxObject *rows = usedRange->querySubObject("Rows");
    int bottomRow = topRow + rows->property("Count").toInt() - 1;
    return bottomRow;
}

void QExcel::setCellString(const QString& cell, const QString& value)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("SetValue(const QString&)", value);
}

void QExcel::setCellFontSize(const QString &cell, int size)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Size", size);
}

void QExcel::setCellTextCenter(const QString &cell)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
}

void QExcel::setCellFontBold(const QString &cell, bool isBold)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Bold", isBold);
}

void QExcel::setCellTextWrap(const QString &cell, bool isWrap)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("WrapText", isWrap);
}

void QExcel::setRowHeight(int row, int height)
{
    QString rowsName;
    rowsName.append(QString::number(row));
    rowsName.append(":");
    rowsName.append(QString::number(row));

    QAxObject * r = sheet->querySubObject("Rows(const QString &)", rowsName);
    r->setProperty("RowHeight", height);
}
